package orm

import (
	"database/sql"
	"reflect"
	"strings"
	"fmt"
	"errors"
	"strconv"
	"regexp"
)

const (
	DriverMysql  = "mysql"
	DriverPgsql  = "postgres"
)

var dr string
var db *sql.DB

func Init(driver string, dns string) (err error) {
	if db, err = sql.Open(driver, dns); err != nil {
		return err
	}

	if err = db.Ping(); err != nil {
		return err
	}

	dr = driver

	return nil
}

func DR() (driver string) {
	return dr
}

func DB() *sql.DB {
	return db
}

func Close() (err error) {
	return db.Close()
}

func CreateDatabase(driver string, dns string) error {

	reMatch := func(expr string, context string, dft string) string {
		re, _ := regexp.Compile(expr)
		rs := re.FindStringSubmatch(context)
		if len(rs) == 2 {
			return rs[1]
		}

		return dft
	}

	reConnect := func(driver string, dns string) (db *sql.DB, err error) {
		ndb, err := sql.Open(driver, dns)
		if err != nil {
			return nil, err
		}

		return ndb, nil
	}

	switch driver {
	case DriverMysql:
		dbName  := reMatch(`\/(\w+)?`, dns, "")
		charset := reMatch(`charset=(\w+)?`, dns, "utf8")
		if dbName != "" {
			dns = strings.Replace(dns, dbName, "", 1)
			ndb, err := reConnect(driver, dns)
			if err != nil {
				return err
			}

			_, err = ndb.Exec("CREATE DATABASE IF NOT EXISTS " + dbName + " DEFAULT CHARSET " + charset + ";")
			if err != nil {
				return err
			}

			return nil
		}

		return errors.New("error: mysql db name is not found")
	case DriverPgsql:
		dbName  := reMatch(`dbname=(\w+)?`, dns, "")
		charset := reMatch(`charset=(\w+)?`, dns, "utf8")
		if dbName != "" {
			dns = strings.Replace(dns, "dbname=" + dbName, "", 1)
			ndb, err := reConnect(driver, dns)
			if err != nil {
				return err
			}

			re, err  := ndb.Exec("SELECT datname FROM pg_catalog.pg_database where datname='" + dbName + "';")
			if err != nil {
				return err
			}

			row, _ := re.RowsAffected()
			if row == 0 {
				_, err = ndb.Exec("CREATE DATABASE " + dbName + " WITH ENCODING = '" + charset + "';")
				if err != nil {
					return err
				}
			}

			return nil
		}

		return errors.New("error: postgres db name is not found")
	default:
		return errors.New("error: db driver is not found")
	}

	return nil
}

func Exec(query string, args ...interface{}) (rows sql.Result, err error) {

	if db == nil {
		panic("database connect fail")
	}

	rs, err := db.Exec(query, args...)
	if err != nil {
		return nil, err
	}

	return rs, err
}

func Query(query string, args ...interface{}) (result []map[string]string, err error) {
	if db == nil {
		panic("database connect fail")
	}

	var rows *sql.Rows

	if rows, err = db.Query(query, args...); err != nil {
		return nil, err
	}

	return Result(rows)
}

func QueryRow(query string, args ...interface{}) *sql.Row {
	if db == nil {
		panic("database connect fail")
	}

	return db.QueryRow(query, args...)
}

func Result(rows *sql.Rows) (result []map[string]string, err error) {
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	values := make([]sql.RawBytes, len(columns))
	scans  := make([]interface{}, len(values))

	for i := range values {
		scans[i] = &values[i]
	}

	for rows.Next() {
		row := make(map[string]string)

		if err := rows.Scan(scans...); err != nil {
			return nil, err
		}

		for i, v := range values {
			row[columns[i]] = string(v)
		}

		result = append(result, row)
	}

	return result, nil
}

func filter(str string) string {
	return strings.Trim(strings.Trim(str, " "), "`")
}

func New(entity Entity) *Model {

	model := &Model{
		entity: 		entity,
		fields: 		make(map[string]string),
		primaryKeys: 	make(map[string]string),
		new:			true,
	}

	model.init()

	return model
}

type Entity interface {
	Init()
}

type Model struct {
	entity 		Entity
	tableName  	string
	fields 		map[string]string
	primaryKeys	map[string]string
	new	    	bool
}

func (m *Model) init() {

	ve := reflect.ValueOf(m.entity).Elem()
	if ve.FieldByName("Model").Type() != reflect.TypeOf(m) {
		panic("orm " + reflect.ValueOf(m.entity).Type().String() +" is not extend *orm.Model")
	}

	ele   := reflect.TypeOf(m.entity).Elem()
	tName := reflect.ValueOf(m).Type().String()

	for i := 0; i < ele.NumField(); i++ {
		name := ele.Field(i).Name
		tag  := ele.Field(i).Tag

		if m.tableName == "" && ele.Field(i).Type.String() == tName {
			if tableName, ok := tag.Lookup("table"); ok {
				m.tableName = tableName
			}

			ve.FieldByName("Model").Set(reflect.ValueOf(m))
		}

		if field, ok := tag.Lookup("field"); ok {
			m.fields[name] = field
		} else if name != "Model" {
			m.fields[name] = strings.ToLower(name)
		}

		if primaryKey, ok := tag.Lookup("pk"); ok {
			m.primaryKeys[name] = primaryKey
		}
	}

	m.entity.Init()
}

func (m *Model) table() string {
	return filter(m.TableName())
}

func (m *Model) comment() string {
	return ""
}

func (m *Model) TableName() string {
	if m.tableName != "" {
		return m.tableName
	}

	return reflect.Indirect(reflect.ValueOf(m.entity)).Type().Name()
}

func (m *Model) Select() *Select {
	slt := &Select{
		model: 		m,
		fields:		[]string{},
		orders:		[]string{},
		wheres:		[]string{},
		whereArgs:	[]interface{}{},
		groups:		[]string{},
		having:     []string{},
		havingArgs: []interface{}{},
		offset:		0,
		limit:		0,
	}

	return slt
}

func (m *Model) ids() map[string]interface{} {
	var ids = make(map[string]interface{})

	ve := reflect.ValueOf(m.entity).Elem()
	for pk, _ := range m.primaryKeys {
		ids[pk] = ve.FieldByName(pk).Interface()
	}

	return ids
}

func (m *Model) ID() interface{} {

	ids := m.ids()
	l := len(ids)

	if l <= 0 {
		return nil
	}else if l == 1 {
		for _, v := range ids {
			return v
		}
	}

	return m.ids
}

func (m *Model) Find(id interface{}) (err error) {
	slt := m.Select()

	switch reflect.TypeOf(id).Kind() {
	case reflect.Int, reflect.Float32, reflect.Float64, reflect.String:
		for k, _ := range m.ids() {
			slt.Where(fmt.Sprintf("`%v` = ?", m.fields[k]), id)
		}
	case reflect.Map:
		for k, v := range id.(map[string]interface{}) {
			if _, ok := m.fields[k]; ok {
				slt.Where(fmt.Sprintf("`%v` = ?", m.fields[k]), v)
			} else {
				slt.Where(fmt.Sprintf("`%v` = ?", k), v)
			}
		}
	default:
		return errors.New("id type is error")
	}

	return slt.GetOne()
}

func (m *Model) Save() (rs bool, err error) {
	var rows int64

	if m.new {
		rows, err = m.insert()
	} else {
		rows, err = m.update()
	}

	if err != nil{
		return false, err
	}

	if rows <= 0 {
		return false, errors.New("save fail")
	}

	return true, nil
}

func (m *Model) Delete() (rs bool, err error) {

	var wheres []string
	var args   []interface{}

	for attr, value := range m.ids() {
		wheres = append(wheres, fmt.Sprintf("`%v` = ?", m.fields[attr]))
		args   = append(args, value)
	}

	str := fmt.Sprintf("DELETE FROM `%v` WHERE %v",
		m.table(), strings.Join(wheres, " AND "))

	result, err := Exec(str, args...)
	if err != nil {
		return false, err
	}

	rows, err := result.RowsAffected()
	if rows >= 0 {
		return true, nil
	}

	return false, errors.New("delete fail")
}

func (m *Model) insert() (rows int64, err error) {
	var fields []string
	var values []string
	var args   []interface{}

	ve := reflect.ValueOf(m.entity).Elem()

	for attr, filed := range m.fields {
		fields = append(fields, filed)
		values = append(values, "?")
		args   = append(args, ve.FieldByName(attr).Interface())
	}

	str := fmt.Sprintf("INSERT INTO `%v` (%v) VALUES (%v)",
		m.table(), "`" + strings.Join(fields, "`, `") + "`", strings.Join(values, ", "))

	result, err := Exec(str, args...)
	if err != nil {
		return 0, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	} else {
		m.parseLastID(id)
	}

	rows, err = result.RowsAffected()
	if err != nil {
		return 0, err
	}

	return rows, err
}

func (m *Model) update() (rows int64, err error) {
	var fields []string
	var wheres []string
	var args   []interface{}

	ids := m.ids()
	ve  := reflect.ValueOf(m.entity).Elem()

	for attr, filed := range m.fields {
		if _, ok := ids[attr]; ok {
			continue
		}

		fields = append(fields, fmt.Sprintf("`%v` = ?", filed))
		args   = append(args, ve.FieldByName(attr).Interface())
	}

	for attr, value := range ids {
		wheres = append(wheres, fmt.Sprintf("`%v` = ?", m.fields[attr]))
		args   = append(args, value)
	}

	str := fmt.Sprintf("UPDATE `%v` SET %v WHERE %v",
		m.table(), strings.Join(fields, ", "), strings.Join(wheres, " AND "))

	result, err := Exec(str, args...)
	if err != nil {
		return 0, err
	}

	rows, err = result.RowsAffected()
	if rows >= 0 {
		return rows, nil
	}

	return 0, nil
}

func (m *Model) bind(row map[string]string) {
	ve := reflect.ValueOf(m.entity).Elem()
	m.new = false

	for attr, filed := range m.fields {
		if value, ok := row[filed]; ok {
			if x, err := stringToValue(value, ve.FieldByName(attr).Type()); err == nil {
				ve.FieldByName(attr).Set(x)
			}
		}
	}
}

func (m *Model) parseLastID(id int64) {
	if id == 0 {
		return
	}

	var autoKey string

	ele := reflect.TypeOf(m.entity).Elem()
	for i := 0; i < ele.NumField(); i++ {
		if reflect.TypeOf(id) != ele.Field(i).Type {
			continue
		}

		if _, ok := ele.Field(i).Tag.Lookup("auto"); ok {
			autoKey = ele.Field(i).Name
			break
		}

		if _, ok := ele.Field(i).Tag.Lookup("pk"); ok {
			autoKey = ele.Field(i).Name
			break
		}
	}

	if autoKey != "" {
		ve := reflect.ValueOf(m.entity).Elem()
		ve.FieldByName(autoKey).Set(reflect.ValueOf(id))
	}
}

func (m *Model) CreateTable() (rs bool, err error) {
	dataType := func(p reflect.Type) (dt string, un bool, dl int) {
		switch p.Kind() {
		case reflect.String :
			return "VARCHAR", false, 255
		case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32:
			return "INT", false, 11
		case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32:
			return "INT", true, 11
		case reflect.Int64:
			return "BIGINT", false, 20
		case reflect.Uint64:
			return "BIGINT", true, 20
		case reflect.Float32, reflect.Float64:
			return "FLOAT", false, 11
		case reflect.Bool:
			return "TINYINT", false, 4
		case reflect.Struct, reflect.Ptr, reflect.Map, reflect.Array, reflect.Slice:
			return "TEXT", false, 0
		}

		return "VARCHAR", false, 255
	}

	type Index struct {
		Name	string
		Fields  []string
	}

	type Field struct {
		Name 			string
		DataType 		string
		DataLength		int
		Comment 		string
		Default 		string
		Unsigned		bool
		AutoIncrement 	bool
		PrimaryKey		bool
		NotNull 		bool
	}

	type Table struct {
		Name 			string				// 表名称
		Engine			string				// 存储引擎
		Comment 		string				// 表描述
		Fields  		[]Field				// 表结构
		Primary			map[string]Index	// 主键
		Indexes			map[string]Index	// 索引
		Uniques			map[string]Index	// 唯一索引
	}

	table := Table {
		Name:		m.table(),
		Comment:	m.comment(),
		Fields:		make([]Field, 0),
		Primary:    make(map[string]Index),
		Indexes:	make(map[string]Index),
		Uniques:	make(map[string]Index),
	}

	TableAppend := func (method string, name string, filed string) bool {
		ele  := reflect.ValueOf(table)
		md   := ele.FieldByName(method)

		if md.Kind() != reflect.Map {
			return false
		}

		maps := md.Interface().(map[string]Index)
		if v, ok := maps[name]; ok {
			v.Name 	 = name
			v.Fields = append(v.Fields, filed)
			md.SetMapIndex(reflect.ValueOf(name), reflect.ValueOf(v))
		} else {
			i := Index{Name:name, Fields: make([]string, 0)}
			i.Fields = append(i.Fields, filed)
			md.SetMapIndex(reflect.ValueOf(name), reflect.ValueOf(i))
		}

		return true
	}

	ele   := reflect.TypeOf(m.entity).Elem()
	tName := reflect.ValueOf(m).Type().String()

	for i := 0; i < ele.NumField(); i++ {
		filed := Field{}
		name  := ele.Field(i).Name
		tag   := ele.Field(i).Tag

		if ele.Field(i).Type.String() == tName {
			if tableComment, ok := tag.Lookup("comment"); ok {
				table.Comment = tableComment
			}
			if tableEngine, ok := tag.Lookup("engine"); ok {
				table.Engine = tableEngine
			}
			continue
		}

		if fieldName, ok := tag.Lookup("field"); ok {
			filed.Name = fieldName
		} else if name != "Model" {
			filed.Name = strings.ToLower(name)
		}

		if _, ok := tag.Lookup("auto"); ok {
			filed.AutoIncrement = true
		}

		if _, ok := tag.Lookup("notnull"); ok {
			filed.NotNull = true
		}

		if pkName, ok := tag.Lookup("pk"); ok {
			filed.PrimaryKey = true
			filed.NotNull 	 = true
			TableAppend("Primary", pkName, filed.Name)
		}

		if l, ok := tag.Lookup("length"); ok {
			li, _ := strconv.Atoi(l)
			filed.DataLength = li
		}

		if dft, ok := tag.Lookup("default"); ok {
			filed.Default = dft
		}

		if comment, ok := tag.Lookup("comment"); ok {
			filed.Comment = comment
		}

		if t, ok := tag.Lookup("type"); ok {
			filed.DataType = strings.ToUpper(t)
		} else {
			dt, un, dl := dataType(ele.Field(i).Type)
			filed.DataType = strings.ToUpper(dt)
			filed.Unsigned = un
			if filed.DataLength == 0 {
				filed.DataLength = dl
			}
		}

		if ixName, ok := tag.Lookup("index"); ok {
			TableAppend("Indexes", ixName, filed.Name)
		}

		if unName, ok := tag.Lookup("unique"); ok {
			TableAppend("Uniques", unName, filed.Name)
		}

		table.Fields = append(table.Fields, filed)
	}

	mysqlSQL := func(table Table) (string) {
		if table.Engine == "" {
			table.Engine = "MyISAM"
		}

		SQL  := "CREATE TABLE `" + table.Name + "` ( \n"
		FSql := make([]string, 0)
		for _, field := range table.Fields {
			length := strconv.FormatInt(int64(field.DataLength), 10)
			extra  := ""

			if field.Unsigned {
				extra += "UNSIGNED"
			}

			if field.Default != "" {
				extra += " DEFAULT '"+field.Default+"'"
			}

			if field.NotNull {
				extra += " NOT NULL"
			} else {
				extra += " NULL"
			}

			if field.AutoIncrement {
				extra += " AUTO_INCREMENT"
			}

			if field.Comment != "" {
				extra += " COMMENT '"+field.Comment+"'"
			}

			FSql = append(FSql, "`"+field.Name+"` "+field.DataType+"("+length+") "+strings.Trim(extra, " "))
		}

		if len(table.Primary) > 0 {
			for k, v := range table.Primary {
				FSql = append(FSql, "CONSTRAINT "+k+" PRIMARY KEY (`"+strings.Join(v.Fields, "`,`")+"`)")
			}
		}

		if len(table.Uniques) > 0 {
			for k, v := range table.Uniques {
				FSql = append(FSql, "UNIQUE INDEX "+k+" (`"+strings.Join(v.Fields, "`,`")+"`)")
			}
		}

		if len(table.Indexes) > 0 {
			for k, v := range table.Indexes {
				FSql = append(FSql, "INDEX "+k+" (`"+strings.Join(v.Fields, "`,`")+"`)")
			}
		}

		SQL += strings.Join(FSql, ",\n")
		SQL += "\n) COMMENT='" + table.Comment + "' ENGINE=" + table.Engine + "; \n"

		return SQL
	}

	var SQL string

	switch DR() {
	case DriverMysql:
		SQL = mysqlSQL(table)
	default:
		return false, errors.New("table create fail")
	}

	// fmt.Println(SQL)

	_, err = Exec(SQL)
	if err != nil {
		return false, err
	}

	return true, nil
}

type Select struct {
	model       *Model
	fields 		[]string
	wheres    	[]string
	whereArgs 	[]interface{}
	orders    	[]string
	groups    	[]string
	having      []string
	havingArgs  []interface{}
	offset    	int
	limit     	int
}

func (slt *Select) Fields(fields ...string) *Select {
	if len(fields) <= 0 {
		return slt
	}

	for _, field := range fields {
		params := strings.Split(filter(field), ",")
		for _, param := range params {
			slt.fields = append(slt.fields, filter(param))
		}
	}

	return slt
}

func (slt *Select) Where(where string, args ...interface{}) *Select {
	if strings.Count(where, "?") != len(args) {
		panic(fmt.Sprintln("where error:", where, ", args:", fmt.Sprintln(args...)))
	}

	slt.wheres 	  = append(slt.wheres, where)
	slt.whereArgs = append(slt.whereArgs, args...)
	return slt
}

func (slt *Select) OrderBy(orders ...string) *Select {
	slt.orders = orders
	return slt
}

func (slt *Select) GroupBy(groups ...string) *Select {
	slt.groups = groups
	return slt
}

func (slt *Select) Having(having string, args ...interface{}) *Select {
	if strings.Count(having, "?") != len(args) {
		panic(fmt.Sprintln("having error:", having, ", args:", fmt.Sprintln(args...)))
	}

	slt.having 	   = append(slt.having, having)
	slt.havingArgs = append(slt.havingArgs, args...)
	return slt
}

func (slt *Select) Offset(offset int) *Select {
	slt.offset = offset
	return slt
}

func (slt *Select) Limit(limit int) *Select {
	slt.limit = limit
	return slt
}

func (slt *Select) ToString() string {
	return slt.compile(false)
}

func (slt *Select) Get() (result []Entity, err error) {
	rs, err := Query(slt.compile(false), slt.getArgs()...)
	if err != nil {
		return nil, err
	}

	for _, row := range rs {
		ve := reflect.New(reflect.TypeOf(slt.model.entity).Elem()).Elem()

		for attr, filed := range slt.model.fields {
			if value, ok := row[filed]; ok {
				if x, err := stringToValue(value, ve.FieldByName(attr).Type()); err == nil {
					ve.FieldByName(attr).Set(x)
				}
			}
		}

		entity := ve.Addr().Interface().(Entity)
		entity.Init()

		for {
			m := *slt.model
			m.entity = entity
			m.new = false

			ve.FieldByName("Model").Set(reflect.ValueOf(&m))
			break
		}

		result = append(result, entity)
	}

	return result, nil
}

func (slt *Select) GetOne() (err error) {
	slt.Limit(1).Offset(0)

	result, err := Query(slt.compile(false), slt.getArgs()...)
	if err != nil {
		return err
	}

	if len(result) <= 0 {
		return errors.New("not found")
	}

	for _, row := range result {
		slt.model.bind(row)
		break
	}

	return nil
}

func (slt *Select) Count() int {
	var count int

	QueryRow(slt.compile(true), slt.getArgs()...).Scan(&count)

	return count
}

func (slt *Select) Delete() (rs bool, err error) {
	str := fmt.Sprintf("DELETE FROM `%v` %v", slt.table(), slt.compileWhere())

	result, err := Exec(str, slt.getArgs()...)
	if err != nil {
		return false, err
	}

	rows, err := result.RowsAffected()
	if rows >= 0 {
		return true, nil
	}

	return false, errors.New("delete fail")
}

func (slt *Select) compile(isCount  bool) string {
	str := "SELECT "

	field := "*"
	if len(slt.fields) > 0 {
		field = strings.Join(slt.fields, ", ")
	}

	if isCount {
		str += fmt.Sprintf("COUNT(*)")
	} else {
		str += fmt.Sprintf("%v", field)
	}

	str += fmt.Sprintf(" FROM `%v`", slt.table())

	str += slt.compileWhere()

	if len(slt.groups) > 0 {
		str += " GROUP BY " + strings.Join(slt.groups, ", ")
	}

	str += slt.compileHaving()

	if len(slt.orders) > 0 {
		str += " ORDER BY " + strings.Join(slt.orders, ", ")
	}

	if slt.limit > 0 {
		str += fmt.Sprintf(" LIMIT %v", slt.limit)
	}

	if slt.offset > 0 {
		str += fmt.Sprintf(" OFFSET %v", slt.offset)
	}

	return str
}

func (slt *Select) compileWhere() string {
	if len(slt.wheres) <= 0 {
		return ""
	}

	str := " WHERE "
	str += fmt.Sprintf("(%v)", strings.Join(slt.wheres, ") AND ("))

	return str
}

func (slt *Select) compileHaving() string {
	if len(slt.having) <= 0 {
		return ""
	}

	str := " HAVING "
	str += fmt.Sprintf("(%v)", strings.Join(slt.having, ") AND ("))

	return str
}

func (slt *Select) getArgs() (args []interface{}) {
	if len(slt.whereArgs) > 0{
		args = append(args, slt.whereArgs...)
	}

	if len(slt.havingArgs) > 0{
		args = append(args, slt.havingArgs...)
	}

	return args
}

func (slt *Select) table() string {
	return slt.model.table()
}

func stringToValue(str string, t reflect.Type) (x reflect.Value, err error) {
	switch t.Kind() {
	case reflect.Int:
		v, _ := strconv.Atoi(str)
		return reflect.ValueOf(v), nil
	case reflect.Uint:
		v, _ := strconv.Atoi(str)
		return reflect.ValueOf(uint(v)), nil
	case reflect.String:
		return reflect.ValueOf(str), nil
	case reflect.Int8:
		v, _ := strconv.ParseInt(str, 10, 8)
		return reflect.ValueOf(v), nil
	case reflect.Uint8:
		v, _ := strconv.ParseInt(str, 10, 8)
		return reflect.ValueOf(uint8(v)), nil
	case reflect.Int16:
		v, _ := strconv.ParseInt(str, 10, 16)
		return reflect.ValueOf(v), nil
	case reflect.Uint16:
		v, _ := strconv.ParseInt(str, 10, 16)
		return reflect.ValueOf(uint16(v)), nil
	case reflect.Int32:
		v, _ := strconv.ParseInt(str, 10, 32)
		return reflect.ValueOf(v), nil
	case reflect.Uint32:
		v, _ := strconv.ParseInt(str, 10, 32)
		return reflect.ValueOf(uint32(v)), nil
	case reflect.Int64:
		v, _ := strconv.ParseInt(str, 10, 64)
		return reflect.ValueOf(v), nil
	case reflect.Uint64:
		v, _ := strconv.ParseInt(str, 10, 64)
		return reflect.ValueOf(uint64(v)), nil
	case reflect.Float32:
		v, _ := strconv.ParseFloat(str, 64)
		return reflect.ValueOf(float32(v)), nil
	case reflect.Float64:
		v, _ := strconv.ParseFloat(str, 64)
		return reflect.ValueOf(v), nil
	case reflect.Bool:
		v, err := strconv.ParseBool(str)
		if err == nil {
			return reflect.ValueOf(v), nil
		}

		if i, _ := strconv.Atoi(str); i > 0 {
			return reflect.ValueOf(true), nil
		} else {
			return reflect.ValueOf(false), nil
		}
	default:
		return x, errors.New("value type is not found")
	}
}